package org.zh.client.tra_aop;


import com.alibaba.druid.util.JdbcUtils;
import com.fasterxml.jackson.core.JsonProcessingException;
import org.springframework.stereotype.Component;
import org.zh.client.config.SpringContextUtil;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import static org.zh.client.config.GlobalStatic.*;
import static org.zh.util.CommonStatic.om;


@Component
public class SQLiteJDBC {


    public static void main(String[] args) {
        SQLiteJDBC.initTable();
    }

    public static void initTable() {
        try {
            Connection conn = DriverManager.getConnection(sqliteUrl);
            Statement ta = conn.createStatement();
            String createTableTA = "CREATE TABLE IF NOT EXISTS taLocalCache " +
                                   "(id INTEGER PRIMARY KEY AUTOINCREMENT," +
                                   "global_tx_id TEXT," +
                                   "root_url TEXT," +
                                   "taSql TEXT," +
                                   "param_json TEXT," +
                                   "commitStatus INTEGER," + //0 insert添加  1 commit成功  2 rollback 成功
                                   "create_time INTEGER)";
            ta.execute(createTableTA);
            Statement tx = conn.createStatement();
            String createTableTX = "CREATE TABLE IF NOT EXISTS txLocalCache " +
                                   "(id INTEGER PRIMARY KEY AUTOINCREMENT," +
                                   "global_tx_id TEXT," +
                                   "method TEXT," +
                                   "returnClass TEXT," +
                                   "param_json TEXT," +
                                   "commitStatus INTEGER," + //0 insert添加  1 commit成功  2 rollback 成功
                                   "create_time INTEGER)";
            tx.execute(createTableTX);
            Statement remote = conn.createStatement();
            String createTable_remote = "CREATE TABLE IF NOT EXISTS remoteCache " +
                                        "(id INTEGER PRIMARY KEY AUTOINCREMENT," +
                                        "global_tx_id TEXT," +
                                        "root_url TEXT," +
                                        "isCanRollback BOOLEAN," +
                                        "nodeURI TEXT," +
                                        "nodeStatus INTEGER," + //1 starter  2 end   3  error
                                        "commitStatus INTEGER," + //0 insert添加  1 commit成功  2 rollback 成功
                                        "create_time INTEGER)";
            remote.execute(createTable_remote);
            ta.close();
            tx.close();
            remote.close();
            conn.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static void initData() {
        SQLiteJDBC sqLiteJDBC = new SQLiteJDBC();
        Connection conn = sqLiteJDBC.getConn();
        try {
            // 1 error  初始化
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery("select * from remoteCache where nodeStatus = 3 and create_time > " + (System.currentTimeMillis() / 1000 - 24 * 60 * 60));
            while (rs.next()) {
                RemoteTransactionBean error = new RemoteTransactionBean(rs.getString(2), rs.getString(3), rs.getBoolean(4), rs.getString(5), rs.getInt(6));
                if (globalErrorCache.containsKey(error.getGlobal_TX_ID())) {
                    globalErrorCache.get(error.getGlobal_TX_ID()).add(error);
                } else {
                    List<RemoteTransactionBean> list = new ArrayList<>(1);
                    list.add(error);
                    globalErrorCache.put(error.getGlobal_TX_ID(), list);
                }
            }
            rs.close();
            statement.close();
            Statement local = conn.createStatement();
            ResultSet localRS = local.executeQuery("select global_tx_id,root_url,taSql,param_json from taLocalCache where commitStatus = 0 and create_time <" + (System.currentTimeMillis() / 1000 - 60));
            List<LocalTransaction> list = new ArrayList<>();
            while (localRS.next()) {
                LocalTransaction lt = new LocalTransaction(null, GlobalType.TA, localRS.getString(2), localRS.getString(3), om.readValue(localRS.getString(4), Object.class));
                list.add(lt);
            }
            DataSource bean = SpringContextUtil.getBean(DataSource.class);
            Connection connection = bean.getConnection();
            Thread.startVirtualThread(() -> {
                list.forEach(lt -> {
                    rollback(lt, connection);
                });
            });
            JdbcUtils.close(connection);
        } catch (SQLException | JsonProcessingException e) {
            throw new RuntimeException(e);
        }

    }

    private static void rollback(LocalTransaction lt, Connection connection) {
        Object[] sqlparams = null;
        String parsedSql = "";
        PreparedStatement ps = null;
        try {
            if (lt.params() instanceof Map) {
                Pattern pattern = Pattern.compile(":([a-zA-Z_][a-zA-Z0-9_]*)");
                Matcher matcher = pattern.matcher(lt.taSql());
                List<Object> paramNames = new ArrayList<>();
                StringBuilder sb = new StringBuilder();
                while (matcher.find()) {
                    paramNames.add(((Map<String, Object>) lt.params()).get(matcher.group(1)));
                    matcher.appendReplacement(sb, "?");
                }
                matcher.appendTail(sb);
                parsedSql = sb.toString();
                sqlparams = paramNames.toArray();
            }
            if (lt.params() instanceof Object[]) {
                parsedSql = lt.taSql();
                sqlparams = (Object[]) lt.params();
            }
            if (lt.params() instanceof Collection<?>) {
                parsedSql = lt.taSql();
                sqlparams = ((Collection<?>) lt.params()).toArray();
            }

            ps = connection.prepareStatement(parsedSql);
            connection.setAutoCommit(false);
            if (sqlparams != null) {
                for (int i = 0; i < sqlparams.length; i++) {
                    ps.setObject(i + 1, sqlparams[i]);
                }
            }
            ps.executeUpdate();
            ps.close();
            connection.commit();
            connection.setAutoCommit(true);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(ps);
        }

    }

    private Connection getConn() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public void insertTA(String global_tx_id, String root_url, String taSql, Object args) {
        Connection conn = getConn();
        try {
            conn.setAutoCommit(true);
            PreparedStatement ps = conn.prepareStatement("insert into taLocalCache (global_tx_id,root_url,taSql,param_json,commitStatus,create_time) values (?,?,?,?,?,?)");
            ps.setString(1, global_tx_id);
            ps.setString(2, root_url);
            ps.setString(3, taSql);
            ps.setString(4, om.writeValueAsString(args));
            ps.setInt(5, 0);
            ps.setLong(6, System.currentTimeMillis() / 1000);
            ps.execute();
            ps.close();
            conn.close();
        } catch (SQLException | JsonProcessingException e) {
            throw new RuntimeException(e);
        }
    }

    public void insertTX(String global_tx_id, String method, String returnClass, Object[] args) {
        Connection conn = getConn();
        try {
            conn.setAutoCommit(true);
            PreparedStatement ps = conn.prepareStatement("insert into txLocalCache (global_tx_id,method,returnClass,param_json,commitStatus,create_time) values (?,?,?,?,?,?)");
            ps.setString(1, global_tx_id);
            ps.setString(2, method);
            ps.setString(3, returnClass);
            ps.setString(4, om.writeValueAsString(args));
            ps.setInt(5, 0);
            ps.setLong(6, System.currentTimeMillis() / 1000);
            ps.execute();
            ps.close();
            conn.close();
        } catch (SQLException | JsonProcessingException e) {
            throw new RuntimeException(e);
        }
    }

    public void insertRemote(String global_tx_id, String root_url, boolean isCanRollback, String nodeURI, int nodeStatus) {
        Connection conn = getConn();
        try {
            conn.setAutoCommit(true);
            PreparedStatement ps = conn.prepareStatement("insert into remoteCache (global_tx_id,root_url,isCanRollback,nodeURI,nodeStatus,commitStatus,create_time) values (?,?,?,?,?,?,?)");
            ps.setString(1, global_tx_id);
            ps.setString(2, root_url);
            ps.setBoolean(3, isCanRollback);
            ps.setString(4, nodeURI);
            ps.setInt(5, nodeStatus);
            ps.setInt(6, 0);
            ps.setLong(7, System.currentTimeMillis() / 1000);
            ps.execute();
            ps.close();
            conn.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public void updateRemote(String global_tx_id) {
        Connection conn = getConn();
        try {
            conn.setAutoCommit(true);
            PreparedStatement ps = conn.prepareStatement("update remoteCache  set commitStatus = ? where global_tx_id = ?");
            ps.setBoolean(1, true);
            ps.setString(2, global_tx_id);
            ps.execute();
            ps.close();
            conn.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public void updateTA(String global_tx_id, int commit) {
        Connection conn = getConn();
        try {
            conn.setAutoCommit(true);
            PreparedStatement ps = conn.prepareStatement("update taLocalCache  set commitStatus = ? where global_tx_id = ?");
            ps.setInt(1, commit);
            ps.setString(2, global_tx_id);
            ps.execute();
            ps.close();
            conn.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public void updateTX(String global_tx_id, int commit) {
        Connection conn = getConn();
        try {
            conn.setAutoCommit(true);
            PreparedStatement ps = conn.prepareStatement("update txLocalCache  set commitStatus = ? where global_tx_id = ?");
            ps.setInt(1, commit);
            ps.setString(2, global_tx_id);
            ps.execute();
            ps.close();
            conn.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
